CREATE OR REPLACE VIEW www.person_info_v8 AS 
 SELECT person.id, person.crsid, 
        CASE
            WHEN person.hide_email THEN NULL::character varying
            ELSE person.email_address
        END AS email_address, person.image_oid, www_person_hid_v2.www_person_hid, (person.surname::text || ', '::text) || COALESCE(person.known_as, person.first_names)::text AS sortable_name, cambridge_college.name AS college_name, cambridge_college.website AS college_website, telephone_numbers.telephone_numbers, 
        CASE
            WHEN website_staff_category_hid.website_staff_category_hid IS NOT NULL THEN website_staff_category_hid.website_staff_category_hid::text
            WHEN (_latest_role.post_category_id = ANY (ARRAY['sc-1'::text, 'sc-9'::text])) OR person.counts_as_academic IS TRUE THEN 'Academic'::text
            WHEN ((_latest_role.post_category_id = ANY (ARRAY['sc-4'::text, 'sc-6'::text, 'sc-7'::text])) OR person.counts_as_postdoc IS TRUE) AND person.counts_as_academic IS NOT TRUE THEN 'Postdoc'::text
            WHEN _latest_role.post_category_id = 'sc-5'::text THEN 'Research assistant'::text
            WHEN _latest_role.post_category_id = 'sc-2'::text THEN 'Academic-Related'::text
            WHEN _latest_role.post_category_id = 'v-8'::text AND person.counts_as_academic IS NOT TRUE THEN 'Emeritus'::text
            ELSE post_category_hid.post_category_hid
        END AS post_category, person.counts_as_academic, s.status_id AS physical_status, r.rigs, pr.primary_rig, rgs.research_groups, rgs.website_addresses, 
        CASE
            WHEN _latest_role.role_tablename = 'post_history'::text THEN post_history.job_title
            ELSE NULL::character varying(120)
        END AS job_title, h.post_category_hid AS research_group_post_category
   FROM ( SELECT _latest_role_v1.person_id, max(_latest_role_v1.post_category_weight) AS max
           FROM www._latest_role_v1
          GROUP BY _latest_role_v1.person_id) lr
   JOIN www._latest_role_v1 ls ON lr.person_id = ls.person_id AND lr.max = ls.post_category_weight
   JOIN person ON person.id = lr.person_id
   JOIN www.post_category_hid h ON h.post_category_id = ls.post_category_id
   LEFT JOIN www_person_hid_v2 ON www_person_hid_v2.person_id = person.id
   LEFT JOIN www.person_research_groups rgs ON person.id = rgs.person_id
   LEFT JOIN www.telephone_numbers ON person.id = telephone_numbers.id
   LEFT JOIN cambridge_college ON person.cambridge_college_id = cambridge_college.id
   LEFT JOIN cache._latest_role ON person.id = _latest_role.person_id
   LEFT JOIN post_history ON post_history.id = _latest_role.role_id
   LEFT JOIN _physical_status_v3 s ON s.person_id = person.id
   LEFT JOIN www.rig_membership_v1 r ON r.id = person.id
   LEFT JOIN www.primary_rig_membership_v1 pr ON pr.id = person.id
   LEFT JOIN www.post_category_hid ON post_category_hid.post_category_id = _latest_role.post_category_id
   LEFT JOIN hotwire.website_staff_category_hid ON website_staff_category_hid.website_staff_category_id = person.website_staff_category_id
  WHERE person.do_not_show_on_website IS FALSE AND person.is_spri IS NOT TRUE
  ORDER BY (person.surname::text || ', '::text) || COALESCE(person.known_as, person.first_names)::text;
